import string
import random

import pymysql
from flask import Flask, json, request, jsonify
import requests
from werkzeug.utils import redirect

app = Flask(__name__)

appId = "wxf2421d70d28fd54c"
appSecret = "27e398696ae445524fd7559cd2c6fffc"


@app.route("/")
def hello_world():
    return "<p>Hello, World!</p>"


# 用户登录获取openid————login
@app.route("/login", methods=['GET', 'POST'])
def login():
    data = request.get_data()
    json_data = json.loads(data.decode("UTF-8"))

    js_code = json_data.get("code")
    nickname = json_data.get("nickName")
    sex = str(json_data.get("sex"))

    url = "https://api.weixin.qq.com/sns/jscode2session?appid=" + appId + "&secret=" \
          + appSecret + "&js_code=" + js_code + "&grant_type=authorization_code"
    response = requests.get(url)
    res = response.json()
    if res:
        db = pymysql.connect(host="localhost",
                             user="root",
                             password="123456",
                             database="ayh",
                             charset="utf8")
        # print("database yes")
        cursor = db.cursor()

        # sql = "INSERT INTO users VALUES (NULL, `res['openid']`, NULL, `sex`, nickname)"
        sql = "INSERT IGNORE INTO `users`(openid, sex, nickName) VALUES (%s, %s, %s)"
        # noinspection PyBroadException
        try:
            cursor.execute(sql, (res['openid'], sex, nickname))
            db.commit()
            return jsonify({"status": 200, "openid": res['openid']})
        except Exception as e:
            print("插入异常！！")
            print(e)
            db.rollback()
            return jsonify({"status": 500})
    else:
        return jsonify({"status": 500})


# 用户获取个人信息————getUserInfo
@app.route("/getUserInfo", methods=['GET'])
def getUserInfo():
    # data = request.get_data()
    # json_data = json.loads(data.decode("UTF-8"))

    # openid = json_data.get("openid")
    openid = request.args.get("openid")
    db = pymysql.connect(host="localhost",
                         user="root",
                         password="123456",
                         database="ayh",
                         charset="utf8")
    # print("database yes")
    cursor = db.cursor()

    sql = "SELECT phone, sex FROM `users` WHERE openid = %s"
    try:
        cursor.execute(sql, openid)
        results = cursor.fetchall()
        for row in results:
            phone = row[0]
            sex = row[1]
            # print(phone)
            # print(sex)
            cursor.close()
            db.close()
            return jsonify({"status": 200, "sex": sex, "phone": phone})
    except Exception as e:
        print("查询出错: " + e)
        db.rollback()
        cursor.close()
        db.close()
        return jsonify({"status": 500, "sex": "未设置", "phone": "未设置"})


# 用户修改性别————updUserSex
@app.route("/updUserSex", methods=['GET', 'POST'])
def updUserSex():
    data = request.get_data()
    json_data = json.loads(data.decode("UTF-8"))
    sex = json_data.get("sex")
    # print(sex)
    openid = json_data.get("openid")
    if sex != '男' and sex != "女":
        return jsonify({"status": 500, "msg": "error!请填写男或女作为自己的性别"})
    db = pymysql.connect(host="localhost",
                         user="root",
                         password="123456",
                         database="ayh",
                         charset="utf8")
    # print("database yes")
    cursor = db.cursor()
    sql = 'UPDATE users SET sex = %s  WHERE openid = %s'
    cursor.execute(sql, (sex, openid))
    db.commit()
    cursor.close()
    db.close()
    return jsonify({"status": 200, "sex": sex})
    # return jsonify({"status": 200, "sex": sex})
    # except Exception as e:
    #     print("查询出错: " + e)
    #     db.rollback()
    #     cursor.close()
    #     db.close()
    #     return jsonify({"status": 500, "msg": "error"})


# 用户修改手机号————updUserPhone
@app.route("/updUserPhone", methods=['PUT'])
def updUserPhone():
    data = request.get_data()
    json_data = json.loads(data.decode("UTF-8"))
    phone = json_data.get("phone")
    # print(phone)
    openid = json_data.get("openid")
    if len(phone) != 11:
        return jsonify({"status": 500, "msg": "error请填写正确的11位手机号码"})
    db = pymysql.connect(host="localhost",
                         user="root",
                         password="123456",
                         database="ayh",
                         charset="utf8")
    # print("database yes")
    cursor = db.cursor()
    sql = 'UPDATE users SET phone = %s  WHERE openid = %s'
    cursor.execute(sql, (phone, openid))
    db.commit()
    cursor.close()
    db.close()
    return jsonify({"status": 200, "phone": phone})
    # return jsonify({"status": 200, "sex": sex})
    # except Exception as e:
    #     print("查询出错: " + e)
    #     db.rollback()
    #     cursor.close()
    #     db.close()
    #     return jsonify({"status": 500, "msg": "error"})


# 加入班级————joinClass
@app.route("/joinClass", methods=['POST'])
def joinClass():
    data = request.get_data()
    json_data = json.loads(data.decode("UTF-8"))

    openid = json_data.get("openid")
    class_code = json_data.get("code")
    db = pymysql.connect(host="localhost",
                         user="root",
                         password="123456",
                         database="ayh",
                         charset="utf8")
    # print("database yes")
    cursor = db.cursor()
    sql1 = "SELECT id FROM classes WHERE code = %s"
    sql2 = "SELECT id, nickName FROM users WHERE openid = %s"
    try:
        cursor.execute(sql1, class_code)
        results = cursor.fetchall()
        for row in results:
            cid = row[0]
        cursor.close()

        cursor = db.cursor()
        cursor.execute(sql2, openid)
        results = cursor.fetchall()
        for row in results:
            uid = row[0]
            nickname = row[1]
        cursor.close()
    except Exception as e:
        print(e)
        db.rollback()
        cursor.close()
        return jsonify({"status": 500, "msg": "班级不存在"})

    sql = "INSERT INTO classes_users(cid, uid, nickName) VALUES ('%d', '%d', '%s')" % (cid, uid, nickname)
    sql3 = "SELECT name FROM classes WHERE id = %d" % cid

    cursor = db.cursor()
    try:
        cursor.execute(sql)
        db.commit()
        cursor.close()

        cursor = db.cursor()
        cursor.execute(sql3)
        names = cursor.fetchall()
        for row in names:
            name = row[0]
        cursor.close()

        sql_num = "UPDATE classes SET num = num+1 WHERE id = %d" % cid
        cursor = db.cursor()
        cursor.execute(sql_num)
        db.commit()
        cursor.close()

        return jsonify({"status": 200, "name": name})
    except Exception as e:
        print(e)
        db.rollback()
        cursor.close()
        db.close()
        return jsonify({"status": 500, "msg": "班级不存在"})


# 创建班级————addClass
@app.route("/addClass", methods=['POST', 'GET'])
def addClass():
    data = request.get_data()
    json_data = json.loads(data.decode("UTF-8"))
    openid = json_data.get("openid")
    name = json_data.get("name")
    sql = "SELECT id FROM users WHERE openid = %s"
    db = pymysql.connect(host="localhost",
                         user="root",
                         password="123456",
                         database="ayh",
                         charset="utf8")
    # print("database yes")
    cursor = db.cursor()
    cursor.execute(sql, openid)
    results = cursor.fetchall()
    for row in results:
        uid = row[0]

    class_code = ''.join(random.sample(string.ascii_letters + string.digits, 8))
    sql_insert = "INSERT INTO classes(uid, name, num, code) VALUES ('%d','%s','%d','%s')" % (uid, name, 0, class_code)
    try:
        cursor.execute(sql_insert)
        db.commit()
        cursor.close()
        db.close()
        return jsonify({"status": 200, "code": class_code})
    except Exception as e:
        print(e)
        db.rollback()
        cursor.close()
        db.close()
        return jsonify({"status": 500, "msg": "创建失败，请重试"})


# 查询已创建班级————getUserClass
@app.route("/getUserClass", methods=['GET'])
def getUserClass():
    openid = request.args.get("openid")
    sql_id = "SELECT id FROM users WHERE openid = %s"
    db = pymysql.connect(host="localhost",
                         user="root",
                         password="123456",
                         database="ayh",
                         charset="utf8")
    # print("database yes")
    cursor = db.cursor()
    cursor.execute(sql_id, openid)

    tea_id = cursor.fetchone()
    # print(tea_id)

    data = []
    users = []
    i_data = {}
    cla = {}
    user = {}
    try:
        sql_class = "SELECT id, name, num, code FROM classes WHERE uid = %d" % tea_id
        cursor.execute(sql_class)
        results = cursor.fetchall()

        for row in results:
            cla["id"] = row[0]
            cla["name"] = row[1]
            cla["num"] = row[2]
            cla["code"] = row[3]

            sql_stu = "SELECT uid FROM classes_users WHERE cid = %d" % cla.get("id")
            cursor.execute(sql_stu)
            uid_group = cursor.fetchall()
            for i in uid_group:
                user["id"] = i[0]

                sql_user = "SELECT phone, sex, nickName FROM users WHERE id = %d" % user.get("id")
                db1 = pymysql.connect(host="localhost",
                                      user="root",
                                      password="123456",
                                      database="ayh",
                                      charset="utf8")
                # print("database yes")
                cur = db1.cursor()
                cur.execute(sql_user)
                info = cur.fetchall()
                for j in info:
                    user["phone"] = j[0]
                    user["sex"] = j[1]
                    user["nickName"] = j[2]

                users.append(user)
                cur.close()
                db1.close()

            i_data["class"] = cla
            i_data["users"] = users
            i_data["hidden"] = False
            i_data["delHidden"] = False

        data.append(i_data)
        cursor.close()
        db.close()
        return jsonify({"status": 200, "data": data})

    except Exception as e:
        print(e)
        db.rollback()
        cursor.close()
        db.close()
        return jsonify({"status": 500})


# 查看消息提醒————getSignMsg
@app.route("/getSignMsg", methods=['GET'])
def getSignMsg():
    openid = request.args.get("openid")
    data = {}
    sign = []
    psign = []
    gsign = []
    lsign = []
    # i_sign = {}
    db = pymysql.connect(host="localhost",
                         user="root",
                         password="123456",
                         database="Ayh",
                         charset="utf8")
    print("database yes")
    cursor = db.cursor()

    # sql = "SELECT name,id from classes WHERE id = (SELECT cid FROM createSign WHERE id=(SELECT csid FROM sign WHERE
    # " \ "uid=(SELECT id FROM users WHERE openid = '%s')))" % openid
    sql1 = "SELECT csid FROM sign WHERE (uid=(SELECT id FROM users WHERE openid = '%s' ) AND finish = '0' ) " % openid
    # sql2 = "SELECT * FROM info inner join info1"
    try:
        cursor.execute(sql1)
        info = cursor.fetchall()
        for i in info:
            i_sign = {}
            print(i[0])
            i_sign["signid"] = i[0]
            sql2 = "SELECT name FROM classes WHERE id =  (SELECT cid FROM createSign WHERE id=%d)" % i[0]
            print(i[0])
            # db1 = pymysql.connect(host="localhost",
            #                       user="root",
            #                       password="123456",
            #                       database="ayh",
            #                       charset="utf8")
            print("database yes2")
            cursor.execute(sql2)
            # cur = db.cursor()
            # cur.execute(sql2)
            info1 = cursor.fetchall()
            for j in info1:
                print(j[0])
                i_sign["name"] = j[0]
            sign.append(i_sign)
            print(sign)
            # cursor.close()
            # db1.close()
        cursor.close()
        db.close()
        data = {
            "sign": sign,
            "psign": psign,
            "gsign": gsign,
            "lsign": lsign
        }
        print(data)
        return jsonify({"status": 200, "data": data})

    except Exception as e:
        print(e)
        db.rollback()
        cursor.close()
        db.close()
        return jsonify({"status": 500, "msg": 0})


# 查询加入的班级————getUserJoinClass
@app.route("/getUserJoinClass", methods=['GET'])
def getUserJoinClass():
    openid = request.args.get("openid")

    sql_user = "SELECT id, nickName FROM users WHERE openid = %s"
    db = pymysql.connect(host="localhost",
                         user="root",
                         password="123456",
                         database="Ayh",
                         charset="utf8")
    # print("连接上了！！！！")
    cursor = db.cursor()
    user = {}
    i_data = {}
    data = []
    cla = {}

    try:
        cursor.execute(sql_user, openid)
        result = cursor.fetchall()
        for row in result:
            user["id"] = row[0]
            # user["nickName"] = row[1]

        sql_cla = "SELECT DISTINCT cid, nickName FROM classes_users WHERE uid = %d" % user.get("id")
        cursor.execute(sql_cla)
        results = cursor.fetchall()
        for row in results:
            cla["id"] = row[0]
            user["nickName"] = row[1]
            sql_info = "SELECT name, num, code FROM classes WHERE id = %d" % cla.get("id")
            db1 = pymysql.connect(host="localhost",
                                  user="root",
                                  password="123456",
                                  database="Ayh",
                                  charset="utf8")
            # print("再一次连接上了！！！！")
            cur = db1.cursor()
            cur.execute(sql_info)
            info = cur.fetchall()

            for i in info:
                cla["name"] = i[0]
                cla["num"] = i[1]
                cla["code"] = i[2]

            cur.close()
            db1.close()
            i_data["class"] = cla
            i_data["hidden"] = False
            i_data["delHidden"] = False
            i_data["showUpd"] = False
            i_data["tempNickName"] = ""
            i_data["user"] = user

            data.append(i_data)
        cursor.close()
        db.close()
        return jsonify({"status": 200, "data": data})
    except Exception as e:
        print(e)
        db.rollback()
        cursor.close()
        db.close()
        return jsonify({"status": 500})


# 用户修改自己的班级昵称————updUserClassNickName
@app.route("/updUserClassNickName", methods=['PUT'])
def updUserClassNickName():
    data = request.get_data()
    json_data = json.loads(data.decode("UTF-8"))
    openid = json_data.get("openid")
    # print(openid)
    cid = json_data.get("cid")
    nickName = json_data.get("nickName")

    db = pymysql.connect(host="localhost",
                         user="root",
                         password="123456",
                         database="Ayh",
                         charset="utf8")
    # print("database yes")
    cursor = db.cursor()
    sql = "UPDATE classes_users SET nickName = '%s'  WHERE cid = '%d'" % (nickName, cid)  # 待更新

    try:
        cursor.execute(sql)
        db.commit()
        cursor.close()
        db.close()
        return jsonify({"status": 200})
    except Exception as e:
        print(e)
        db.rollback()
        cursor.close()
        db.close()
        return jsonify({"status": 500, "msg": 0})


# 用户退出已加入的班级————exitClass
@app.route("/exitClass", methods=['PUT'])
def exitClass():
    data = request.get_data()
    json_data = json.loads(data.decode("UTF-8"))
    openid = json_data.get("openid")
    cid = json_data.get("cid")
    db = pymysql.connect(host="localhost",
                         user="root",
                         password="123456",
                         database="Ayh",
                         charset="utf8")
    # print("database yes")
    cursor = db.cursor()
    sql = "DELETE FROM classes_users WHERE cid = '%d' " % cid
    sql1 = "UPDATE classes SET num = num - 1 WHERE(id= '%d' and uid = (SELECT id FROM users WHERE openid = '%s') )" % (
        cid, openid)
    try:
        cursor.execute(sql)
        cursor.execute(sql1)
        db.commit()
        cursor.close()
        db.close()
        return jsonify({"status": 200})
    except Exception as e:
        print(e)
        db.rollback()
        cursor.close()
        db.close()
        return jsonify({"status": 500, "msg": 0})


# 用户删除已创建的班级————delClass
@app.route("/delClass", methods=['PUT'])
def delClass():
    data = request.get_data()
    json_data = json.loads(data.decode("UTF-8"))
    openid = json_data.get("openid")
    cid = json_data.get("cid")
    db = pymysql.connect(host="localhost",
                         user="root",
                         password="123456",
                         database="Ayh",
                         charset="utf8")
    # print("database yes")
    cursor = db.cursor()
    sql = "DELETE FROM classes WHERE id = %d" % cid
    sql1 = "DELETE FROM classes_users WHERE cid = %d" % cid
    try:
        cursor.execute(sql)
        cursor.execute(sql1)
        db.commit()
        cursor.close()
        db.close()
        return jsonify({"status": 200})
    except Exception as e:
        print(e)
        db.rollback()
        cursor.close()
        db.close()
        return jsonify({"status": 500, "msg": 0})


# 用户删除班级成员————teaDelClassUser
@app.route("/teaDelClassUser", methods=['PUT'])
def teaDelClassUser():
    data = request.get_data()
    json_data = json.loads(data.decode("UTF-8"))
    openid = json_data.get("openid")
    cid = json_data.get("cid")
    uid = json_data.get("uid")
    db = pymysql.connect(host="localhost",
                         user="root",
                         password="123456",
                         database="Ayh",
                         charset="utf8")
    # print("database yes")
    cursor = db.cursor()
    sql = "DELETE FROM classes_users WHERE cid = %d and uid = %d" % (cid, uid)
    sql1 = "UPDATE classes SET num=num-1 WHERE uid = %d" % uid
    try:
        cursor.execute(sql)
        cursor.execute(sql1)
        db.commit()
        cursor.close()
        db.close()
        return jsonify({"status": 200})
    except Exception as e:
        print(e)
        db.rollback()
        cursor.close()
        db.close()
        return jsonify({"status": 500, "msg": 0})
